package com.thon.dao.project.impl;

import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import org.springframework.stereotype.Repository;

import com.thon.dao.project.ProjectDao;
import com.thon.dao.util.AbstractDao;
import com.thon.dao.util.Page;
import com.thon.entity.project.Project;
import com.thon.entity.project.ProjectContributionView;
import com.thon.entity.user.User;

/**
 * @file ProjectDaoImpl.java
 * @author Zemo
 * @mail zemochen@gmail.com
 * @data 2013年11月16日 下午5:56:12
 * @description: TODO
 */
@Repository
public class ProjectDaoImpl extends AbstractDao<Project> implements ProjectDao {

	public ProjectDaoImpl() {
		super(Project.class);
	}

	@Override
	public Page<Project> findAllProjects(int status,Page<Project> page,String queryStr) {
		String queryString;
		if(queryStr.equals("")||queryStr==null)		{
			if(status==-1)
			{
				queryString= "FROM Project p  ORDER BY p.createdDate DESC";
				return findPage(page, queryString);
			}
			else
			{
			queryString= "FROM Project p where p.status = ? ORDER BY p.createdDate DESC";
			return findPage(page, queryString,status);
			}
		}
		else		{
			if(status==-1){
				queryString= "FROM Project p where  p.name like '%"+queryStr+"%' ORDER BY p.createdDate DESC";
				return findPage(page, queryString);
			}
			else
			{
			queryString= "FROM Project p where p.status = ? and p.name like '%"+queryStr+"%' ORDER BY p.createdDate DESC";
			return findPage(page, queryString,status);
			}
		}
			
	}

	@Override
	public List<Project> listProjectByStatus(int status) {
		
		String queryString = "FROM Project WHERE status = ?  ORDER BY createdDate DESC";
		return findList(queryString, null, status);
	}
	
	@Override
	public Page<Project> findProjectPage(Page<Project> page, Integer stauts) {

		String querysString = "FROM Project p WHERE p.status = ?  ORDER BY createdDate DESC";

		return findPage(page, querysString, stauts);
	}

	@Override
	public Page<Project> findProjectByAction(Page<Project> page, int userId,
			String action,int status) {
		String queryString;
		if (action.equals("start")) {
			
			if(status==-1)
			{
				queryString = "FROM Project p WHERE p.institution.id IN (SELECT i.id FROM Institution i WHERE i.user.id = ?) ORDER BY createdDate DESC";

				return findPage(page, queryString, userId);
			}
			else{				
			queryString = "FROM Project p WHERE p.institution.id IN (SELECT i.id FROM Institution i WHERE i.user.id = ?) AND status=? ORDER BY createdDate DESC";

			return findPage(page, queryString, userId,status);
			}
		} else {
			if(status==-1)
			{
				queryString = "SELECT * FROM ym_projects WHERE id IN(SELECT project_id FROM "
						+ action + " WHERE user_id = ?) ORDER BY created_date DESC";

				return findPageBySql(page, queryString, null, userId);
			}
			else
			{
			queryString = "SELECT * FROM ym_projects WHERE id IN(SELECT project_id FROM "
					+ action + " WHERE user_id = ?) AND status=? ORDER BY created_date DESC";

			return findPageBySql(page, queryString, null, userId,status);
			}
		}
	}

	@Override
	public Page<Project> findProjectByCategory(Page<Project> page,
			int categoryId,int status) {

		String queryString = "FROM Project WHERE projectCategory.id = ? AND status = ?  ORDER BY createdDate DESC";
		return findPage(page, queryString, categoryId,status);
	}

	/**
	 * 当到project表中查询时by为project表中的字段（status，category_id,institute_id）时，
	 * 到project表中根据{by}的id查询 当到其他表中查询时by为到那个实体类中查询
	 */
	@Override
	public Long count(String by, int status) {
		String sqlString = null;
		if (by.equals("status") ||  by.equals("institute_id")) {
			sqlString = "SELECT COUNT(*) FROM ym_projects WHERE " + by + "=? ";
		}else if(by.equals("supportCount")){
			sqlString="SELECT COUNT(*) FROM ym_project_supports WHERE project_id=?";
		}else if (by.equals("category_id")) {
			sqlString = "SELECT COUNT(*) FROM ym_projects WHERE " + by + "=? AND status = 5 ";
		}
		else if (by.equals("institute_id")) {
			sqlString = "SELECT COUNT(*) FROM ym_projects WHERE " + by + "=? AND status = 5 ";
		}
		else {
			sqlString = "SELECT count(*) FROM ym_projects WHERE id IN( SELECT project_id FROM "
					+ by + " WHERE user_id=? )";
		}
		return countBySql(sqlString, status);
	}

	@Override
	public List<Project> listProjectBy(String by, Object object) {

		String queryString = "FROM Project project WHERE " + by + " =? ORDER BY created_date DESC";
		return findList(queryString, null, object);
	}

	@Override
	public Page<Project> findProjectSort(Page<Project> page, String by,int status) {

		String queryString;
		if(by.equals("0"))
			queryString = "FROM Project  WHERE status = ?  ORDER BY created_date DESC";
		else
			queryString = "FROM Project  WHERE status = ? AND tags like '%gold%' ORDER BY created_date DESC";
		return findPage(page, queryString,status);
	}

	@Override
	public List<Object> listProjectStatistiBy(Integer projectId, String from) {
		String sqlString = "";
		if (from != null && !from.equals("")) {
			sqlString = "SELECT created_date,COUNT(1) FROM ym_project_" + from
					+ " WHERE project_id=? GROUP BY DAYOFYEAR(created_date)";
		}
		return (List<Object>) findListBySql(sqlString, projectId);
	}

	@Override
	public Long getTopestByDate(Integer projectId, Long date) {
		DateFormat df = new SimpleDateFormat("yyyy-MM-dd");
		String dateStr;
		dateStr = df.format(new Date());
		if (date != null) {
			dateStr = df.format(new Date(date));
		}
		String sqlString ="SELECT SUM(ym_project_supports.product_amount*ym_products.price) "
				+ " FROM ym_project_supports "
				+ " LEFT JOIN ym_products "
				+ " ON ym_products.id=ym_project_supports.product_id "
				+ " WHERE ym_project_supports.project_id= "+projectId
				+ " AND ym_project_supports.created_date LIKE '"+dateStr+"%'"
				+ " GROUP BY ym_project_supports.user_id "
				+ " ORDER BY SUM(ym_project_supports.product_amount*ym_products.price) "
				+ " DESC LIMIT 0,1";
		Object result=findOneBySQL(sqlString);
		if (result!=null) {
			return Long.parseLong(result.toString());
		} else {
			return 0L;
		}
	}

	@Override
	public Page<Project> findProjectSortByRemainDay(Page<Project> page,int status) {
		
		String sqlString="SELECT * FROM ym_projects p WHERE p.status = ? and p.tags like '%small%' ORDER BY p.created_date DESC";
		return findPageBySql(page,sqlString,null,status);
	}

	@Override
	public Page<Project> findProjectPageByRecommend(Page<Project> page,int status) {
		if(status==2){
			String sqlString="SELECT projects.* "
					+ "FROM ym_project_recommends recomends RIGHT OUTER JOIN ym_projects projects "
					+ "ON recomends.project_id=projects.id WHERE projects.tags like '%needrec%' "
					+ "GROUP BY projects.id "
					+ "ORDER BY COUNT(recomends.user_id ) ";
			return findPageBySql(page, sqlString, null);
		}else{
			return null;
		}
	}
	
	@Override
	public List<Object> listTopSupports(Integer projectId,Integer num){
		String sqlString ="SELECT supports.project_id,supports.user_id"
				+ " ,SUM(supports.product_amount*products.price) supportsums,supports.created_date"
				+ " FROM ym_project_supports supports"
				+ " LEFT JOIN ym_products products"
				+ " ON products.id=supports.product_id"
				+ " WHERE supports.project_id=? "
				+ " GROUP BY supports.user_id"
				+ " ORDER BY supportsums DESC"
				+ " LIMIT 0,?";
		return findListBySql(sqlString, projectId,num);
	}
	@Override
	public List<Object> listTopShares(Integer projectId,Integer num){
		String sqlString = "SELECT shares.project_id, shares.user_id,users.name,shares.id,shares.created_date"
				+ " ,SUM(shares.click_count) clicks,users.fund"
				+ " FROM ym_project_shares shares "
				+ " LEFT JOIN ym_users users"
				+ " ON users.id=shares.user_id"
				+ " WHERE shares.project_id=?"
				+ " GROUP BY shares.user_id"
				+ " ORDER BY SUM(shares.click_count) DESC "
				+ " LIMIT 0,?";
		return findListBySql(sqlString, projectId,num);
	}
	@Override
	public Long getSupportSum(Integer projectId,Integer userId){
		String sqlString="SELECT SUM(supports.product_amount*products.price)"
				+ " FROM ym_project_supports supports"
				+ " LEFT JOIN ym_products products"
				+ " ON products.id=supports.product_id"
				+ " WHERE supports.project_id=? "
				+ " AND supports.user_id=?";
		Object sum=findOneBySQL(sqlString, projectId,userId);
		if(sum!=null){
			return Long.valueOf(sum.toString());
		}
		return 0l;
	}
	@Override
	public Object getShare(Integer projectId,Integer userId){
		String sqlString="SELECT shares.id,SUM(shares.click_count) clicks,shares.created_date "
				+ " FROM ym_project_shares shares"
				+ " WHERE shares.project_id=? "
				+ " AND shares.user_id=?"; 
		return findOneBySQL(sqlString, projectId,userId);
	}

	@Override
	public Page<Project> findProjectByRegion(Page<Project> page, int regionId,int status) {
		
		String queryString = "FROM Project p WHERE p.cityRegion.id = ? AND status = ? ORDER BY createdDate DESC";
		return findPage(page, queryString, regionId,status);
	}

	@Override
	public Page<Project> findProjectByTags(Page<Project> page, String tags) {
		
		String queryString = "FROM Project WHERE tags like ? ORDER BY createdDate DESC";
		tags = "%"+tags+",%";
		return findPage(page, queryString,tags);
	}
 
	@Override
	public Project getProjectByInst(int institutionId, int status) {
		
		String queryString = "FROM Project p WHERE p.institution.id = ? AND p.status = ?";
		return findOne(queryString, null, institutionId,status);
	}

	@Override
	public Long countProjectByStatus(int institutionId, int status) {
		String sqlString="select count(*) from ym_projects p WHERE p.institute_id=?";
		if(status!=-1)
		{	sqlString += " AND p.status =?";
			return countBySql(sqlString, institutionId,status);}
		else
			return countBySql(sqlString, institutionId);
	}
	@Override
	public List<Project> listProjectsByStatus(int institutionId, int status) {
		String queryString="FROM Project p WHERE p.institution.id=? AND p.status =? ORDER BY createdDate DESC";
		return findList(queryString, null, institutionId,status);
	}

	@Override
	public Long countProjectByCategory(int categoryId) {
		String queryString = "select count(*) from ym_projects WHERE category_id = ?";
		return countBySql(queryString, categoryId);
	}
	
	@Override
	public Long countProjectByCategory(int categoryId,int status) {
		String queryString = "select count(*) from ym_projects WHERE category_id = ? AND status = ?";
		return countBySql(queryString, categoryId,status);
	}

	@Override
	public List<Project> listALLProjectName() {
		String queryString = "select id,name FROM Project WHERE status = ? ORDER BY createdDate DESC";
		return findList(queryString, null,5);
	}

	@Override
	public Page<Project> findProjectByCategory(Page<Project> page, int categoryId) {
		String queryString="FROM Project p WHERE p.projectCategory.id = ?  ORDER BY createdDate DESC";
		return findPage(page, queryString, categoryId);
	}

	@Override
	public Page<Project> findProjectByBenefit(Page<Project> page, int benefitId) {
		String queryString;
		if(benefitId==0)
		{  
			queryString = "FROM Project  WHERE status =5 ORDER BY createdDate DESC";
			return findPage(page, queryString);
		}
		else
			  queryString = "FROM Project WHERE status =5 AND projectBenefit.id = ? ORDER BY createdDate DESC";
		return findPage(page, queryString, benefitId);
	}

	@Override
	public Page<Project> findProjectByOpen(Page<Project> page, int openId) {
		String queryString = "FROM  Project WHERE projectOpen.id = ? ORDER BY createdDate DESC";
		return findPage(page, queryString, openId);
	}

}
